import pandas as pd
from mysql_in import mysql_sql_df
from mysql_in import mysql_to_sql
# 显示所有列
pd.set_option('display.max_columns', None)
def dim_store():
    # 获取store表数据
    df_store=mysql_sql_df('store')
    # 获取address表信息
    df_address = mysql_sql_df('address')
    # 列链接 左链接
    df_store=pd.merge(left=df_store,right=df_address,how='left',on='address_id')
    # 获取city表信息客户城市信息
    df_city=mysql_sql_df('city')
    # 列链接 左链接
    df_store=pd.merge(left=df_store,right=df_city,how='left',on='city_id')
    # 获取country表
    df_country=mysql_sql_df('country')
    df_country.drop(labels=['last_update'],axis=1,inplace=True)
    # 列链接，左
    df_store=pd.merge(left=df_store,right=df_country,how='left',on='country_id')
    # 获取staff表
    df_staff=mysql_sql_df('staff')

    df_staff.drop(labels=['last_update'],axis=1,inplace=True)
    # 创建manager_staff_id
    df_staff['manager_staff_id']=df_staff['staff_id']
    # 列链接，左
    df_store=pd.merge(left=df_store,right=df_staff,how='left',on='manager_staff_id')
    # print(df_store)
    df_store=df_store[['store_id_x','store_name','address','district','postal_code','phone','city','country','staff_id','first_name','last_name','last_update']]
    # 更名store_id_x改store_id
    df_store=df_store.rename(columns={'store_id_x':'store_id'})
    # 写入mysql
    mysql_to_sql(df_store,'dim_store')